package br.jonasLab.financistoweb.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.jonasLab.financistoweb.utils.Utils;
import br.jonasLab.financistoweb.vo.BalancoMesAnoVO;
import br.jonasLab.financistoweb.vo.DetalheBalacoVO;
import br.jonasLab.financistoweb.vo.SaldoFimMesVO;

public class BalancoMesAnoDAO extends BaseDAO {
	
	public List<BalancoMesAnoVO> balancoMesAno() throws SQLException{
		List<BalancoMesAnoVO> list = new ArrayList<BalancoMesAnoVO>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("	strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') AS MES_ANO,");
		sql.append("	SUM(CASE WHEN trf.category_id IN (25,41,121,112,124,36)  AND trf.to_account_id IN (0,1,2,12) THEN ");
		sql.append("		(CASE WHEN trf.from_amount<0 THEN trf.from_amount*-1 ELSE trf.from_amount END) ");
		sql.append("	 ELSE 0 END) AS VR_GANHOS, ");
		sql.append("	SUM(CASE WHEN trf.category_id IN (3,119) THEN (trf.to_amount) ELSE 0 END) AS VR_RETIDO, ");
		sql.append("	SUM(CASE WHEN trf.category_id = 18 THEN (trf.from_amount) ELSE 0 END) AS VR_PGTO_CARTAO,");
		sql.append("	SUM(CASE WHEN trf.category_id NOT IN (25,41,121,112,124,2,3,18,32,42.119,126,37,36,42,31,118,-1,30,28) ");
		sql.append("		AND trf.to_account_id IN (0,11) AND trf.from_account_id IN (1,2,12,14) THEN (trf.from_amount) ELSE 0 END) ");
		sql.append("	AS VR_GASTOS ");
		sql.append("FROM transactions trf ");
		sql.append("GROUP BY strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') ");
		sql.append("ORDER BY trf.datetime;");
		PreparedStatement ps = this.getConnection().prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			list.add(retVo(rs));
		}
		rs.close();
        ps.close();
		return list;
	}
	
	private BalancoMesAnoVO retVo(ResultSet rs) throws SQLException {
		BalancoMesAnoVO vo = new BalancoMesAnoVO();
		vo.setMesAno(rs.getString("MES_ANO"));
		vo.setGanho(Utils.divideValores(rs.getDouble("VR_GANHOS"),100,2));
		vo.setRetido(Utils.divideValores(rs.getDouble("VR_RETIDO"),100,2));
		vo.setPagCartao(Utils.divideValores(rs.getDouble("VR_PGTO_CARTAO"),100,2));
		vo.setGasto(Utils.divideValores(rs.getDouble("VR_GASTOS"),100,2));
		return vo;
	}
	
	public List<SaldoFimMesVO> saldoFimMes() throws SQLException{
		List<SaldoFimMesVO> list = new ArrayList<SaldoFimMesVO>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("	strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') AS MES_ANO, ");
		sql.append("	SUM(CASE WHEN (trf.from_account_id = 2 OR trf.to_account_id = 2) THEN (CASE WHEN trf.to_account_id = 2 THEN trf.to_amount ELSE trf.from_amount END) END) AS SANTANDER, ");
		sql.append("	SUM(CASE WHEN (trf.from_account_id = 1 OR trf.to_account_id = 1) THEN (CASE WHEN trf.to_account_id = 1 THEN trf.to_amount ELSE trf.from_amount END) END) AS MAOS, ");
		sql.append("	SUM(CASE WHEN (trf.from_account_id = 12 OR trf.to_account_id = 12) THEN (CASE WHEN trf.to_account_id = 12 THEN trf.to_amount ELSE trf.from_amount END) END) AS BB ");
		sql.append("FROM transactions trf ");
		sql.append("WHERE  trf.category_id <> -1 ");
		sql.append("GROUP BY strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') ");
		sql.append("ORDER BY trf.datetime;");
		PreparedStatement ps = this.getConnection().prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			SaldoFimMesVO vo = new SaldoFimMesVO();
			vo.setMesAno(rs.getString("MES_ANO"));
			vo.setSantander(Utils.divideValores(rs.getDouble("SANTANDER"), 100, 2));
			vo.setMaos(Utils.divideValores(rs.getDouble("MAOS"), 100, 2));
			vo.setBancoBrasil(Utils.divideValores(rs.getDouble("BB"), 100, 2));
			list.add(vo);
		}
		rs.close();
        ps.close();
		return list;
	}
	
	public List<DetalheBalacoVO> listDetalheBalanco(String mesAno,String sinCategories, String categories, String sinToAccount, String toAccount, String sinFromAccount, String fromAccount) throws SQLException{
		List<DetalheBalacoVO> listRet = new ArrayList<DetalheBalacoVO>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("	strftime('%d/%m/%Y %H:%M:%S', trf.datetime/1000, 'unixepoch', 'localtime') AS DATA,");
		sql.append("	(CASE WHEN trf.from_amount<0 THEN (trf.from_amount/100.00)*-1 ELSE trf.from_amount/100.00 END) AS VALOR,");
		sql.append("    cat.title AS CATEGORIA,");
		sql.append("    pay.title AS PAGADO ");
		sql.append("FROM transactions trf ");
		sql.append("	INNER JOIN category cat ON trf.category_id = cat._id ");
		sql.append("	LEFT JOIN payee pay ON trf.payee_id = pay._id ");
		sql.append("WHERE "); 
		sql.append("	trf.category_id "+sinCategories+" ("+categories+") ");
		if(!toAccount.equals("")){
			sql.append("    AND trf.to_account_id "+sinToAccount+" ("+toAccount+") ");
		}
		if(!fromAccount.equals("")){
			sql.append("    AND trf.from_account_id "+sinFromAccount+" ("+fromAccount+") ");
		}
		sql.append("    AND strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') = '"+mesAno+"'");
		sql.append("ORDER BY trf.datetime;");
		PreparedStatement ps = this.getConnection().prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			DetalheBalacoVO vo = new DetalheBalacoVO();
			vo.setData(rs.getString("DATA"));
			vo.setValor(rs.getDouble("VALOR"));
			vo.setCategoria(rs.getString("CATEGORIA"));
			vo.setPagado(rs.getString("PAGADO"));
			listRet.add(vo);
		}
		rs.close();
        ps.close();
		return listRet;
	}
	public List<DetalheBalacoVO> listRankingCategoriaPorMes(String mesAno,String sinCategories, String categories, String sinToAccount, String toAccount, String sinFromAccount, String fromAccount) throws SQLException{
		List<DetalheBalacoVO> listRet = new ArrayList<DetalheBalacoVO>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("	cat.title AS CATEGORIA, ");
		sql.append("	SUM(trf.from_amount)/100.00 AS VALOR ");
		sql.append("FROM transactions trf ");
		sql.append("	INNER JOIN category cat ON trf.category_id = cat._id ");
		sql.append("WHERE ");
		sql.append("	trf.category_id "+sinCategories+" ("+categories+") ");
		sql.append("    AND trf.to_account_id "+sinToAccount+" ("+toAccount+") ");
		sql.append("    AND trf.from_account_id "+sinFromAccount+" ("+fromAccount+") ");
		sql.append("    AND strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') = '"+mesAno+"'");
		sql.append("GROUP BY trf.category_id ");
		sql.append("ORDER BY SUM(trf.from_amount);");
		PreparedStatement ps = this.getConnection().prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			DetalheBalacoVO vo = new DetalheBalacoVO();
			vo.setCategoria(rs.getString("CATEGORIA"));
			vo.setValor(rs.getDouble("VALOR"));
			listRet.add(vo);
		}
		return listRet;
	}
	
}















